# 作者：吾那尔汗
# 名称：各学校学籍报表统计脚本
# 版本：v1.1

import os
import numpy as np
from openpyxl import Workbook
from openpyxl import load_workbook

# 指定文件夹路径
folder_path = 'data'
year_datas = {f'year_{i}':[] for i in range(20)}

# 遍历所有文件读取Excel文件内容
for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx') or filename.endswith('.xls'):  # 只处理 Excel 文件
        file_path = os.path.join(folder_path, filename)
        
        # 加载工作簿
        wb = load_workbook(file_path)
        # 选择 Sheet1
        sheet = wb['Sheet1']
        
        # 读取第8行至第33行，第1列至第36列的数据
        data = []
        for row in sheet.iter_rows(min_row=14, max_row=33, min_col=1, max_col=33):
            row_data = list()
            for cell in row:
                # 如果单元格内容为空，那么填入0值
                if cell.value == None:
                    cell.value = 0
                if isinstance(cell.value,int):
                    row_data.append(cell.value)
            data.append(row_data)

        # 求和运算并添加到数据字典
        for i in range(20):
            year_datas[f'year_{i}'].append(data[i])

#         # 打印数据到屏幕
        # print(f"文件: {filename}")

# 假定result.xlsx已经存在并包括一个Sheet1
result_wb = load_workbook('summary.xlsx')
result_sheet = result_wb['Sheet1']

def writer(line,year):
    # 为指定的行写入指定岁数的数据求和结果
    # line 指定行
    # year 指定岁数
    # 需要写入数据的行名
    excel_column_names = [
    "B", "C", "D", "E", "F", "G", "H", "I", "J",
    "K", "M", "N","P", "Q", "R", "S", "T","U", 
    "V", "Y", "Z", "AA", "AC", "AD","AE","AF","AG"
    ]

    for index,value in enumerate(excel_column_names):
        # 求和结果写入指定单元格
        result_sheet[f'{value}{line}'] = np.sum(year,axis=0)[index]

    # 保存修改
    result_wb.save('summary.xlsx')
    return True

# 调用函数写入汇总数据
for i in range(20):
    row = 14 + i
    # 14行写入0岁汇总数据
    writer(row,year_datas[f'year_{i}'])

# 关闭工作簿
result_wb.close()

